package zy.dao.stock.price.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.stock.price.StockPriceDAO;
import zy.entity.stock.price.T_Stock_Price;
import zy.entity.stock.price.T_Stock_PriceList;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.StringUtil;

@Repository
public class StockPriceDAOImpl extends BaseDaoImpl implements StockPriceDAO{
	
	@Override
	public Integer count(Map<String, Object> params) {
		Object pc_isdraft = params.get("pc_isdraft");
		Object pc_ar_state = params.get("pc_ar_state");
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object pc_manager = params.get("pc_manager");
		Object pc_number = params.get("pc_number");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_stock_price t");
		sql.append(" WHERE 1=1");
		if (StringUtil.isNotEmpty(pc_isdraft)) {
			sql.append(" AND pc_isdraft = :pc_isdraft ");
		}
		if (StringUtil.isNotEmpty(pc_ar_state)) {
			sql.append(" AND pc_ar_state = :pc_ar_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND pc_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND pc_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(pc_manager)) {
			sql.append(" AND pc_manager = :pc_manager ");
		}
		if (StringUtil.isNotEmpty(pc_number)) {
			sql.append(" AND INSTR(pc_number,:pc_number) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<T_Stock_Price> list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object pc_isdraft = params.get("pc_isdraft");
		Object pc_ar_state = params.get("pc_ar_state");
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object pc_manager = params.get("pc_manager");
		Object pc_number = params.get("pc_number");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT pc_id,pc_number,pc_date,pc_manager,pc_remark,pc_ar_state,pc_ar_date,pc_isdraft,pc_sysdate,pc_us_id,companyid");
		sql.append(" FROM t_stock_price t");
		sql.append(" WHERE 1=1");
		if (StringUtil.isNotEmpty(pc_isdraft)) {
			sql.append(" AND pc_isdraft = :pc_isdraft ");
		}
		if (StringUtil.isNotEmpty(pc_ar_state)) {
			sql.append(" AND pc_ar_state = :pc_ar_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND pc_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND pc_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(pc_manager)) {
			sql.append(" AND pc_manager = :pc_manager ");
		}
		if (StringUtil.isNotEmpty(pc_number)) {
			sql.append(" AND INSTR(pc_number,:pc_number) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY pc_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Stock_Price.class));
	}

	@Override
	public T_Stock_Price load(Integer pc_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT pc_id,pc_number,pc_date,pc_manager,pc_remark,pc_ar_state,pc_ar_date,pc_isdraft,pc_sysdate,pc_us_id,companyid");
		sql.append(" FROM t_stock_price t");
		sql.append(" WHERE pc_id = :pc_id");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("pc_id", pc_id),
					new BeanPropertyRowMapper<>(T_Stock_Price.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Stock_Price load(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT pc_id,pc_number,pc_date,pc_manager,pc_remark,pc_ar_state,pc_ar_date,pc_isdraft,pc_sysdate,pc_us_id,companyid");
		sql.append(" FROM t_stock_price t");
		sql.append(" WHERE pc_number = :pc_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("pc_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Stock_Price.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Stock_Price check(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pc_id,pc_number,pc_ar_state,pc_remark,companyid");
		sql.append(" FROM t_stock_price t");
		sql.append(" WHERE pc_number = :pc_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("pc_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Stock_Price.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public List<T_Stock_PriceList> detail_list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pcl_id,pcl_number,pcl_pd_code,pcl_oldprice,pcl_newprice,t.companyid,pd_no,pd_name,pd_unit");
		sql.append(" FROM t_stock_pricelist t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.pcl_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND pcl_number = :pcl_number");
		sql.append(" AND t.companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY pcl_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Stock_PriceList.class));
	}
	
	@Override
	public List<T_Stock_PriceList> detail_list_only(String pc_number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pcl_id,pcl_pd_code,pcl_oldprice,pcl_newprice,companyid");
		sql.append(" FROM t_stock_pricelist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND pcl_number = :pcl_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY pcl_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("pcl_number", pc_number).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Stock_PriceList.class));
	}
	
	@Override
	public List<T_Stock_PriceList> temp_list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pcl_id,pcl_pd_code,pcl_oldprice,pcl_newprice,pcl_us_id,t.companyid,pd_no,pd_name,pd_unit");
		sql.append(" FROM t_stock_pricelist_temp t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.pcl_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND pcl_us_id = :pcl_us_id");
		sql.append(" AND t.companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY pcl_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Stock_PriceList.class));
	}
	
	@Override
	public List<T_Stock_PriceList> temp_list_forsave(Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pcl_id,pcl_pd_code,pcl_oldprice,pcl_newprice,pcl_us_id,t.companyid");
		sql.append(" FROM t_stock_pricelist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND pcl_us_id = :pcl_us_id");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY pcl_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("pcl_us_id", us_id).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Stock_PriceList.class));
	}

	@Override
	public List<String> temp_check(Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pcl_pd_code");
		sql.append(" FROM t_stock_pricelist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND pcl_us_id = :pcl_us_id");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), new MapSqlParameterSource().addValue("pcl_us_id", us_id).addValue("companyid", companyid), String.class);
	}
	
	@Override
	public void temp_save(List<T_Stock_PriceList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_stock_pricelist_temp");
		sql.append(" (pcl_pd_code,pcl_oldprice,pcl_newprice,pcl_us_id,companyid)");
		sql.append(" VALUES");
		sql.append(" (:pcl_pd_code,:pcl_oldprice,:pcl_newprice,:pcl_us_id,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}

	@Override
	public void temp_update(T_Stock_PriceList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_stock_pricelist_temp");
		sql.append(" SET pcl_newprice = :pcl_newprice");
		sql.append(" WHERE 1=1");
		sql.append(" AND pcl_id = :pcl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}

	@Override
	public void temp_del(Integer pcl_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_stock_pricelist_temp");
		sql.append(" WHERE pcl_id=:pcl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("pcl_id", pcl_id));
	}

	@Override
	public void temp_clear(Integer us_id, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_stock_pricelist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND pcl_us_id = :pcl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),
				new MapSqlParameterSource().addValue("pcl_us_id", us_id).addValue("companyid", companyid));
	}
	
	@Override
	public void save(T_Stock_Price price, List<T_Stock_PriceList> details) {
		String prefix = CommonUtil.NUMBER_PREFIX_STOCK_PRICE + DateUtil.getYearMonthDateYYYYMMDD();
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT CONCAT(:prefix,f_addnumber(MAX(pc_number))) AS new_number");
		sql.append(" FROM t_stock_price");
		sql.append(" WHERE 1=1");
		sql.append(" AND INSTR(pc_number,:prefix) > 0");
		sql.append(" AND companyid = :companyid");
		String new_number = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("prefix", prefix).addValue("companyid", price.getCompanyid()), String.class);
		price.setPc_number(new_number);
		sql.setLength(0);
		sql.append(" INSERT INTO t_stock_price");
		sql.append(" (pc_number,pc_date,pc_manager,pc_remark,pc_ar_state,pc_ar_date,pc_isdraft,pc_sysdate,pc_us_id,companyid)");
		sql.append(" VALUES");
		sql.append(" (:pc_number,:pc_date,:pc_manager,:pc_remark,:pc_ar_state,:pc_ar_date,:pc_isdraft,:pc_sysdate,:pc_us_id,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(price),holder);
		price.setPc_id(holder.getKey().intValue());
		for(T_Stock_PriceList item:details){
			item.setPcl_number(price.getPc_number());
		}
		sql.setLength(0);
		sql.append(" INSERT INTO t_stock_pricelist");
		sql.append(" (pcl_number,pcl_pd_code,pcl_oldprice,pcl_newprice,companyid)");
		sql.append(" VALUES");
		sql.append(" (:pcl_number,:pcl_pd_code,:pcl_oldprice,:pcl_newprice,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void update(T_Stock_Price price, List<T_Stock_PriceList> details) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_stock_price");
		sql.append(" SET pc_date=:pc_date");
		sql.append(" ,pc_manager=:pc_manager");
		sql.append(" ,pc_remark=:pc_remark");
		sql.append(" ,pc_ar_state=:pc_ar_state");
		sql.append(" ,pc_ar_date=:pc_ar_date");
		sql.append(" ,pc_us_id=:pc_us_id");
		sql.append(" WHERE pc_id=:pc_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(price));
		for(T_Stock_PriceList item:details){
			item.setPcl_number(price.getPc_number());
		}
		sql.setLength(0);
		sql.append(" INSERT INTO t_stock_pricelist");
		sql.append(" (pcl_number,pcl_pd_code,pcl_oldprice,pcl_newprice,companyid)");
		sql.append(" VALUES");
		sql.append(" (:pcl_number,:pcl_pd_code,:pcl_oldprice,:pcl_newprice,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	
	@Override
	public void updateApprove(T_Stock_Price price) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_stock_price");
		sql.append(" SET pc_ar_state=:pc_ar_state");
		sql.append(" ,pc_ar_date = :pc_ar_date");
		sql.append(" WHERE pc_id=:pc_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(price));
	}
	
	@Override
	public void approve_updateProductPrice(List<T_Stock_PriceList> details) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_base_product");
		sql.append(" SET pd_cost_price = :pcl_newprice");
		sql.append(" WHERE 1=1");
		sql.append(" AND pd_code = :pcl_pd_code");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void del(String pc_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_stock_price");
		sql.append(" WHERE pc_number=:pc_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("pc_number", pc_number).addValue("companyid", companyid));
		sql.setLength(0);
		sql.append(" DELETE FROM t_stock_pricelist");
		sql.append(" WHERE pcl_number=:pcl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("pcl_number", pc_number).addValue("companyid", companyid));
	}
	
	@Override
	public void deleteList(String pc_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_stock_pricelist");
		sql.append(" WHERE pcl_number=:pcl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("pcl_number", pc_number).addValue("companyid", companyid));
	}
	
}
